package com.bestv.search.management.datasyn;

import com.bestv.search.common.dataSource.DataSourceKey;
import com.bestv.search.common.dataSource.DataSourceKeyUtil;
import com.bestv.search.common.model.*;
import com.bestv.search.common.model.uid.UID;
import com.bestv.search.common.util.CharUtil;
import com.bestv.search.common.util.ChineseCharToEn;
import com.bestv.search.common.util.CommonUtil;
import com.bestv.search.common.util.StringConst;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.configuration.reloading.FileChangedReloadingStrategy;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Michael Wang
 */

// $Id$
public class VisDbProgramSyncer extends ProgramSyncerImpl {

    final static String sDBDriver = "oracle.jdbc.driver.OracleDriver";
    final static String filterFile = "filterWords.properties";

    final static int singleVod = 1000;
    final static int progValid = 1;
    final static int limit = 2000;

    String orclUrl = null;
    String encoding = "ISO8859_1";
    String user = null;
    String pass = null;
    String programView = null;
    String[] activeStatusArray;

    public VisDbProgramSyncer(Map<String, String> p) throws Exception {
        this.setProps(p);
        justDoIt();
    }

    private static String formatProgramName(String name) {
        Pattern pattern;
        Matcher matcher;
        String[] formats1 = new String[]{"(.*)(新闻new\\s*[0-9]{6})-{0,1}(.+)", "(.*)(新专题\\s*[0-9]{4,6})-{0,1}(.+)",
                "(.*)(新闻更新\\s*[0-9]{6})-{0,1}(.+)", "(.*)(更新新闻\\s*[0-9]{6})-{0,1}(.+)",
                "(.*)(新闻\\s*[0-9]{6})-{0,1}(.+)", "(.*)(影讯\\s*[0-9]{6})-{0,1}(.+)", "(.*)(娱闻\\s*[0-9]{6})-{0,1}(.+)",
                "(.*)(娱乐专题\\s*[0-9]{6})-{0,1}(.+)", "(.*)(音乐节目\\s*[0-9]{6})-{0,1}(.+)",
                "(.*)(凤凰新闻\\s*[0-9]{6})-{0,1}(.+)", "(.*)(法治新闻\\s*[0-9]{6})-{0,1}(.+)",
                "(.*)(超体\\s*[0-9]{6})-{0,1}(.+)", "(.*)(游戏\\s*[0-9]{6})-{0,1}(.+)", "(.*)(游戏板砖\\s*[0-9]{6})-{0,1}(.+)",
                "(.*)(游戏休闲\\s*[0-9]{6})-{0,1}(.+)", "(.*)(生活\\s*[0-9]{6})-{0,1}(.+)", "(.*)(财闻\\s*[0-9]{6})-{0,1}(.+)"};
        for (String format : formats1) {
            pattern = Pattern.compile(format);
            matcher = pattern.matcher(name);
            if (matcher.matches()) {
                if (matcher.group(1).equals("")) {
                    name = matcher.group(3);
                } else {
                    String header = matcher.group(1);
                    if (header.endsWith("-"))
                        header = header.substring(0, header.lastIndexOf("-"));
                    name = "[" + header + "]" + matcher.group(3);
                }
                break;
            }
        }

        String[] formats2 = new String[]{"(回看)-{0,1}(.+)([0-9]{6})-{0,1}(新闻).*",
                "(回看)-{0,1}(.+)([0-9]{6})-{0,1}(娱乐).*", "(回看)-{0,1}(.+)([0-9]{6})-{0,1}(法治).*",
                "(回看)-{0,1}(.+)([0-9]{6})-{0,1}(生活).*", "(回看)-{0,1}(.+)([0-9]{6})-{0,1}(财经).*",
                "(回看)-{0,1}(.+)([0-9]{6})-{0,1}(纪实).*"};
        for (String format : formats2) {
            pattern = Pattern.compile(format);
            matcher = pattern.matcher(name);
            if (matcher.matches()) {
                name = matcher.group(2);
                if (name.endsWith("-")) {
                    name = name.substring(0, name.lastIndexOf("-"));
                }
                break;
            }
        }

        String[] formats3 = new String[]{"娱乐专题\\s*(.+)([榜]{0,1})([0-9]{6})", "音乐\\s*(.+)([榜]{0,1})([0-9]{6})",
                "(.+)([0-9]{6})-{0,1}(新闻).*", "(.+)([0-9]{6})-{0,1}(娱乐).*", "(.+)([0-9]{6})-{0,1}(法治).*",
                "(.+)([0-9]{6})-{0,1}(财经).*", "(.+)([0-9]{6})-{0,1}(纪实).*"};
        for (String format : formats3) {
            pattern = Pattern.compile(format);
            matcher = pattern.matcher(name);
            if (matcher.matches()) {
                name = matcher.group(1);
                if (name.endsWith("-")) {
                    name = name.substring(0, name.lastIndexOf("-"));
                }
                break;
            }
        }

        return name;
    }

    @Override
    public boolean init(Map<String, String> properties) {
        if (!initLastSyncTime(StringConst.BESTV_VIS, 1, StringConst.VIS_PROGRAM_SYNC_TIME)) {
            return false;
        }
        if (props.get("orclUrl") == null || props.get("orclUrl").length() == 0) {
            logger.error("orclUrl is empty!");
            result = false;
            return false;
        }
        if (props.get("program") == null || props.get("program").length() == 0) {
            logger.error("program view is empty!");
            result = false;
            return false;
        }
        if (props.get("encoding") == null || props.get("encoding").length() == 0) {
            logger.warn("encoding is empty, use ISO8859_1");
        } else {
            encoding = props.get("encoding");
        }
        orclUrl = props.get("orclUrl");
        user = props.get("user");
        pass = props.get("pass");
        programView = props.get("program");
        activeStatusArray = props.get("activeStatus").split("_");
        return true;
    }

    @Override
    public boolean updateProgram() throws Exception {
        logger.info("Now read from vis db program and update to search's");
        thisSyncTime = lastSyncSuccessfullTime;

        // 搜索系统过滤词词组
        String[] filtersWords = getFilterWords();

        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        int offset = 0;
        int maxCount = 0;

        String syncMessage = "Success sync program from Vis system @ " + new Date();
        try {
            Class.forName(sDBDriver);
            conn = DriverManager.getConnection(orclUrl, user, pass);
            stmt = conn.createStatement();

            String activeStatusConditions = "";
            for (int k = 0; k < activeStatusArray.length; k++) {
                if (k == 0) {
                    activeStatusConditions = " (active_status = " + activeStatusArray[k];
                } else {
                    activeStatusConditions += " or active_status = " + activeStatusArray[k];
                }

                if (k == (activeStatusArray.length - 1)) {
                    activeStatusConditions += ") and ";
                }
            }

            boolean isValid, isNew;
            DataSourceKey dsKey = DataSourceKeyUtil.convert(systemCache.getLocalDataSourceKey());

            rs = stmt.executeQuery("select count(1) from " + programView + " where " + activeStatusConditions + " type in ('"
                    + singleVod + "') and update_time >= " + "to_date('" + timeformat.format(lastSyncSuccessfullTime )+ "','YYYY-MM-DD HH24:MI:SS')");

            while(rs.next()){
                maxCount = rs.getInt(1);
            }
            logger.debug("the count of match condition table "+ programView+" is: "+maxCount);

            while(offset<maxCount){
                rs = stmt.executeQuery("select * from (select a.*,rownum rn from (select * from "+ programView +" where "+
                        activeStatusConditions + "type in ('"+ singleVod +"') and update_time >=" + "to_date('" +
                                timeformat.format(lastSyncSuccessfullTime) + "','YYYY-MM-DD HH24:MI:SS')"+
                        " order by update_time) a where rownum <="+(offset+limit)+")b where b.rn>="+offset+"");

                logger.debug("a new batch begins at offset: " + offset+" and maxCount:"+maxCount);
                logger.info("A new batch begins at program_update_time " + timeformat.format(lastSyncSuccessfullTime));

                while (rs.next()) {
                    Date now = new Date();
                    try {
                        isValid = rs.getInt("status") == progValid;
                        Date ut = rs.getTimestamp("update_time");
                        if (thisSyncTime == null || ut.after(thisSyncTime)) {
                            thisSyncTime = ut;
                        }
                        int type = Integer.parseInt(rs.getString("type"));
                        if (type == singleVod) {
                            Program program = new Program();
                            program.setHitCount(0);
                            List<Program> pl = pm.getProgramByCode(dsKey, rs.getString("code"));
                            if (pl != null && pl.size() > 0) {
                                isNew = false;
                                for (int x = 0; x < pl.size(); x++) {
                                    Program pg = pl.get(x);
                                    if (x == 0) {
                                        program = pg;
                                        program.setUpdateFlag(isValid ? 1 : 2);
                                    } else {
                                        pg.setUpdateFlag(2);
                                        pg.setUpdateTimeForIndex(now);
                                        pg.setUpdateTimeMillis(Long.valueOf(updatetimeformat.format(ut)));
                                        vm.saveUpdateInfo(dsKey, pg);

                                        // Update InfoHeaders' updateTime and status
                                        infoHeaderCommand.updateInfoHeadersTimeAndStatus(pg);
                                    }
                                }
                            } else {
                                isNew = true;
                                program.setUpdateFlag(isValid ? 0 : 2);
                            }
                            program.setCode(rs.getString("code"));
                            program.setUpdateTime(ut);

                            String name = unifyStr(rs.getString("name"));
                            String original_name = name;

                            // Fix bug: 新闻类节目名称包含冗余信息
                            // 例如，"新闻09022012张贵英全盘翻供 不确认被击毙者是周克华" 应该不包含"新闻09022012"
                            name = formatProgramName(name);

                            // Check whether the program's name contains words need to be filtered
                            if (containsSubWordInArray(filtersWords, name)) {
                                // Contains, set updateFlag =2
                                program.setUpdateFlag(2);
                                logger.info("Name contains word that need to be filtered " + program.getCode());
                            }

                            program.setName(name);
                            // 更新 name_py字段
                            String abbreviation = ChineseCharToEn.getAllFirstLetter(name);
                            String splitTokens = splitToken(name);
                            program.setNamePY(abbreviation + " " + CharUtil.charToNum(abbreviation) + " " + splitTokens);

                            program.setStatus(rs.getInt("status"));

                            String p_type = unifyStr(rs.getString("content_type"));
                            // 过滤源数据中的剧集
                            if ("电视剧".equals(p_type))
                                p_type = "电影";
                            BizType bizType = bm.getBizTypeByName(dsKey, p_type);
                            if (bizType == null) {
                                p_type = "其他";
                                bizType = bm.getBizTypeByName(dsKey, p_type);
                            }
                            program.setP_type(p_type);
                            program.setBizType(bizType);

                            program.setGenre(unifyStr(rs.getString("genre")));
                            // Check whether the program's genre contains words need to be filtered
                            if (containsSubWordInArray(filtersWords, program.getGenre())) {
                                // Contains, set updateFlag =2
                                program.setUpdateFlag(2);
                                logger.info("Genre contains word that need to be filtered, offline info by mediaCode "
                                        + program.getCode());
                            }

                            String kws = rs.getString("keywords");
                            Set<Keyword> keys = new HashSet<Keyword>();
                            if (kws != null && kws.trim().length() != 0) {
                                String[] keywords = unifyStr(rs.getString("keywords")).split("[\\[\\]\\-,;、 ]+");
                                for (String key : keywords) {
                                    if (key == null || "".equals(key))
                                        continue;
                                    Keyword k = km.getKeywordByName(dsKey, key);
                                    if (k == null) {
                                        k = new Keyword();
                                        k.setKeyword(key);
                                    }
                                    keys.add(k);
                                }
                            }
                            program.setKeywords(keys);
                            String tgs = rs.getString("tags");
                            Set<Tag> ts = new HashSet<Tag>();
                            if (tgs != null && tgs.trim().length() != 0) {
                                String[] tags = unifyStr(rs.getString("tags")).split("[\\[\\]\\-,;、 ]+");
                                for (String tg : tags) {
                                    if (tg == null || "".equals(tg))
                                        continue;
                                    Tag t = tm.getTagByName(dsKey, tg);
                                    if (t == null) {
                                        t = new Tag();
                                        t.setTag(tg);
                                    }
                                    ts.add(t);
                                }

                                // 设置节目是否收费
                                if (tgs.contains("看大片") || tgs.contains("首映"))
                                    program.setIsCharge(1);
                            }
                            program.setTags(ts);

                            // Check whether the program's tag contains words need to be filtered
                            if (containsSubWordInArray(filtersWords, tgs)) {
                                // Contains, set updateFlag =2
                                program.setUpdateFlag(2);
                                logger.info("Tags contains word that need to be filtered, offline info by mediaCode "
                                        + program.getCode());
                            }

                            Type infoType = new Type(3L, "vod", "节目");
                            if ("新闻".equals(p_type))
                                infoType = new Type(6L, "news", "新闻");
                            program.setType(infoType);
                            program.setIsSeries(false);
                            program.setDescription(unifyStr(rs.getString("description")));
                            String actors = unifyStr(rs.getString("actor_display"));
                            program.setActors(actors);
                            if (program.getActorsPY() == null || "".equals(program.getActorsPY())) {
                                String actorsStr = ChineseCharToEn.getAllFirstLetter(actors);
                                program.setActorsPY(actorsStr + " " + CharUtil.charToNum(actorsStr));
                            }
                            String directors = unifyStr(rs.getString("writer_display"));
                            program.setDirectors(directors);
                            if (program.getDirectorsPY() == null || "".equals(program.getDirectorsPY())) {
                                String directorStr = ChineseCharToEn.getAllFirstLetter(directors);
                                program.setDirectorsPY(directorStr + " " + CharUtil.charToNum(directorStr));
                            }
                            program.setRegion(unifyStr(rs.getString("region")));
                            program.setIssueYear(rs.getInt("issue_year") == 0 ? null : yearformat.parse(String.valueOf(rs
                                    .getInt("issue_year"))));
                            program.setUpdateTimeForIndex(now);
                            program.setUpdateTimeMillis(Long.valueOf(updatetimeformat.format(ut)));
                            program.setDomain(rs.getString("domain"));
                            program.setHd(rs.getInt("hd_flag"));

                            // Adds field CATEGORY
                            program.setCategory(rs.getString("CATEGORY"));
                            // Adds field LANGUAGE
                            String language = rs.getString("LANGUAGE");
                            if (!CommonUtil.isNumber(language)) {
                                program.setLanguage(language);
                            }

                            if (program.getName().indexOf("HD") != -1 || program.getName().indexOf("高清") != -1) {
                                program.setHd(1);
                            }

                            if (original_name.startsWith("影讯") || original_name.startsWith("HD-影讯")) {
                                // content_flag枚举值：1-正片 2-预告片 3-影讯
                                program.setContent_flag(3);

                                // re-set p_type and bizType
                                String new_p_type = "影讯";
                                program.setP_type(new_p_type);
                                program.setBizType(getBizType(new_p_type, dsKey));
                            } else if (original_name.endsWith("-预告片")) {
                                program.setContent_flag(2);

                                // re-set p_type and bizType
                                String new_p_type = "预告片";
                                program.setP_type(new_p_type);
                                program.setBizType(getBizType(new_p_type, dsKey));
                            } else {
                                program.setContent_flag(1);
                            }

                            if (isNew) {
                                program.setId(new Long(UID.getUID(program)));
                            }
                            vm.saveUpdateInfo(dsKey, program);

                            // Update InfoHeaders' updateTime and status
                            infoHeaderCommand.updateInfoHeadersTimeAndStatus(program);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        logger.error("read program error! pcode=" + rs.getString("code"));
                    }
                }

                offset+=limit;
            }

            logger.debug("sync program table for se finished");
        } catch (Exception e) {
            System.out.println(e);
            syncMessage = "Failed sync program from Vis system @ " + new Date();
            result = false;
            throw e;
        } finally {
            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
            if (conn != null)
                conn.close();

            // Update Sync status.
            updateSyncStatus(StringConst.BESTV_VIS, 1, StringConst.VIS_PROGRAM_SYNC_TIME, syncMessage, thisSyncTime);
        }

        return true;
    }

    String unifyStr(String s) throws UnsupportedEncodingException {
        if (s == null)
            return null;
        String toCode = encoding.equalsIgnoreCase("iso-8859-1") ? "gbk" : "utf-8";
        return new String(s.getBytes(encoding), toCode).trim();
    }

    private String[] getFilterWords() {
        PropertiesConfiguration config;
        try {
            config = new PropertiesConfiguration(filterFile);
            config.setReloadingStrategy(new FileChangedReloadingStrategy());
            String filterStr = config.getString("filter");
            return filterStr.split("_");
        } catch (Exception e) {
            logger.error("Gets filter word failed " + e.getMessage());
        }

        return null;
    }

    private boolean containsSubWordInArray(String[] array, String word) {
        if (array == null || array.length == 0 || word == null) {
            return false;
        } else {
            for (String wordInArray : array) {
                if (word.contains(wordInArray)) {
                    return true;
                }
            }
        }
        return false;
    }

    private BizType getBizType(String p_type, DataSourceKey dsKey) {
        BizType bizType = bm.getBizTypeByName(dsKey, p_type);
        if (bizType == null) {
            bizType = new BizType();
            bizType.setPtype(p_type);
            // default to 3
            bizType.setLevel(3);
            bizType.setValue(p_type);
            bm.saveBizTypes(dsKey, bizType);
            bizType = bm.getBizTypeByName(dsKey, p_type);
        }
        return bizType;
    }
}
